﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FindIt.BLL;
using MySql.Data.MySqlClient;
using System.Configuration;

namespace FindIt.DAL
{
    public class WebPageDB
    {
        private MySqlConnection conn; 
        private MySqlCommand cmd;

        public WebPageDB()
        {
            conn = new MySqlConnection(ConfigurationManager.AppSettings["connectionString"]);
            cmd = new MySqlCommand();
            cmd.Connection = conn;
        }

        public void AddWebPage(WebPage webPage)
        {
            cmd.CommandText = "INSERT INTO tblWebPage (Title, URL, Size, IsRestricted, UploadDate, LastUpdate, PageRank, NumViews, LastViewed, Status, Owner) "
                                        + "VALUES(@title, @url, @size, @isRestricted, @uploadDate, @lastUpdate, @pageRank, @numViews, @lastViewed, @status, @owner)";

            cmd.Parameters.Add("@title", MySqlDbType.VarChar).Value = webPage.Title;
            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = webPage.URL;
            cmd.Parameters.Add("@size", MySqlDbType.Int32).Value = webPage.Size;
            cmd.Parameters.Add("@isRestricted", MySqlDbType.Bit).Value = webPage.IsRestricted;
            cmd.Parameters.Add("@uploadDate", MySqlDbType.DateTime).Value = webPage.UploadDate;
            cmd.Parameters.Add("@lastUpdate", MySqlDbType.DateTime).Value = webPage.LastUpdate;
            cmd.Parameters.Add("@pageRank", MySqlDbType.Int16).Value = webPage.PageRank;
            cmd.Parameters.Add("@numViews", MySqlDbType.Int32).Value = webPage.NumViews;
            cmd.Parameters.Add("@lastViewed", MySqlDbType.DateTime).Value = webPage.LastViewed;
            cmd.Parameters.Add("@status", MySqlDbType.Enum).Value = webPage.Status;
            cmd.Parameters.Add("@owner", MySqlDbType.VarChar).Value = webPage.Owner;
            
            try
            {   
                conn.Open();
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }

            catch (MySqlException ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }
        }

        public void AddKeyWords(Dictionary<string,int> keywords, string url)
        {
            cmd.CommandText = "INSERT INTO tblWebPageKeyword (Keyword, WebPageURL, Count) "
                                        + "VALUES(@keyword, @webPageURL, @count)";
            try
            {
                foreach (string keyword in keywords.Keys)
                {
                    cmd.Parameters.Add("@keyword", MySqlDbType.VarChar).Value = keyword;
                    cmd.Parameters.Add("@webPageURL", MySqlDbType.VarChar).Value = url;
                    cmd.Parameters.Add("@count", MySqlDbType.Int32).Value = keywords[keyword];

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    conn.Close();
                }
            }

            catch (MySqlException ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

        public MySqlDataReader PerformSearch(string[] queryParts)
        {
            string sql = "SELECT WebPageURL FROM tblWebPageKeyword WHERE";

            int i = 0;
            foreach (string query in queryParts)
            {
                if (i == queryParts.Length - 1)
                    sql += " (Keyword = '" + query + "')";
                else
                    sql += " (Keyword = '" + query + "') OR";
                i++;
            }
                
            sql += " GROUP BY WebPageURL HAVING COUNT(*) = " + i;

            cmd.CommandText = sql;

            cmd.Parameters.Clear();

            MySqlDataReader dr;

            try
            {
                conn.Open();
                dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            return dr;
        }

        public MySqlDataReader GetWebPageByURL(string url)
        {
            cmd.CommandText = "SELECT * FROM tblWebPage WHERE URL = @url";

            cmd.Parameters.Clear();
            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;

            MySqlDataReader dr;

            try
            {
                conn.Open();
                dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            return dr;
        }
    }
}